﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Serialization;

namespace LambdaToSql.EntityModel
{
    [Serializable]
    [DataContract]
    internal abstract class QueryEntity<T> : BaseEntity<T> where T : class, new()
    {
        #region Base

        /// <summary>
        /// sql 参数
        /// </summary>
        [DataMember]
        public virtual List<LambdaToSql.EntityModel.DataParameter> Parameters { get; set; }

        /// <summary>
        /// 查询字段
        /// </summary>
        [DataMember]
        public virtual List<string> Fields { get; set; }

        /// <summary>
        /// 条件
        /// </summary>
        [DataMember]
        public virtual StringBuilder Where { get; set; }

        /// <summary>
        /// 排序
        /// </summary>
        [DataMember]
        public virtual List<string> OrderBy { get; set; }

        /// <summary>
        /// 分组
        /// </summary>
        [DataMember]
        public virtual List<string> GroupBy { get; set; }

        /// <summary>
        /// top N
        /// </summary>
        [DataMember]
        public virtual int? Top { get; set; }

        /// <summary>
        /// 页码
        /// </summary>
        [DataMember]
        public virtual int? Index { get; set; }

        #endregion

        #region GetStr

        /// <summary>
        /// 最终查询字符串 只读
        /// </summary>
        [DataMember]
        public virtual string SqlStr
        {
            get
            {
                var sql = "";
                if (!string.IsNullOrEmpty(this.GroupByStr))
                {
                    sql = string.Format(SqlTemplate, this.TopStr + " " + this.FieldsStr, this.TableName + " " + this.WhereStr + " " + this.GroupByStr);//还需要优化
                }
                else
                {
                    sql = string.Format(SqlTemplate, this.TopStr + " " + this.FieldsStr, this.TableName + " " + this.WhereStr + " " + this.OrderByStr);
                }
                return sql;
            }
        }

        /// <summary>
        /// 函数最终查询字符串 只读
        /// </summary>
        [DataMember]
        public virtual string SqlFunStr
        {
            get
            {
                var sql = string.Format(SqlTemplate, this.FieldsStr, this.TableName + " " + this.WhereStr);
                if (!string.IsNullOrEmpty(this.TopStr))
                {
                    sql = string.Format(SqlTemplate, this.TopStr + " " + this.FieldsStr, this.TableName + " " + this.WhereStr + " " + this.OrderByStr);
                }
                return sql;
            }
        }

        /// <summary>
        /// 条件字符串 只读
        /// </summary>
        [DataMember]
        public virtual string WhereStr
        {
            get
            {
                var result = "";
                if (!string.IsNullOrEmpty(this.Where.ToString()))
                {
                    result = " where " + this.Where;
                }
                return result;
            }
        }

        /// <summary>
        /// 排序字符串 只读
        /// </summary>
        [DataMember]
        public virtual string OrderByStr
        {
            get
            {
                var result = "";
                if (this.OrderBy.Count > 0)
                {
                    result = this.OrderByTemplate + string.Join(",", this.OrderBy);
                }
                else
                {
                    if (!string.IsNullOrEmpty(this.OrderByDefaultTemplate))
                    {
                        result = this.OrderByDefaultTemplate + string.Join(",", this.OrderBy);
                    }
                }
                return result;
            }
        }

        /// <summary>
        /// 分组字符串 只读
        /// </summary>
        [DataMember]
        public virtual string GroupByStr
        {
            get
            {
                var result = "";
                if (this.GroupBy.Count > 0)
                {
                    result = this.GroupByTemplate + string.Join(",", this.GroupBy);
                }
                return result;
            }
        }

        /// <summary>
        /// 前N行字符串 只读
        /// </summary>
        [DataMember]
        public virtual string TopStr
        {
            get
            {
                var result = "";
                if (this.Top != null)
                {
                    result = string.Format(this.TopTemplate, this.Top);
                }
                return result;
            }
        }

        /// <summary>
        /// 查询字段字符串 只读
        /// </summary>
        [DataMember]
        public virtual string FieldsStr
        {
            get
            {
                var result = "*";
                if (this.GroupBy.Count > 0)
                {
                    result = string.Join(",", this.GroupBy);
                }
                else if (this.Fields.Count > 0)
                {
                    result = string.Join(",", this.Fields);
                }
                return result;
            }
        }
        #endregion

        #region Sql Template

        /// <summary>
        /// 查询结构模板
        /// </summary>
        [DataMember]
        public virtual string SqlTemplate
        {
            get
            {
                return "select {0} from {1} ";
            }
        }
        /// <summary>
        /// Join模板
        /// </summary>
        [DataMember]
        public virtual string JoinTemplate
        {
            get
            {
                return "{0} join {1}{2} on {3} ";
            }
        }
        /// <summary>
        /// 排序模板 默认主键排序
        /// </summary>
        [DataMember]
        public virtual string OrderByDefaultTemplate
        {
            get
            {
                var orderby = "";
                var tableName = (new T()).GetType().Name;
                if (this.Context != null)
                {
                    if (this.Context.Table.ContainsKey(tableName))
                    {
                        var primaryName = this.Context.Table[tableName].PrimaryName;
                        if (!string.IsNullOrEmpty(primaryName))
                        {
                            orderby = "order by " + primaryName + " desc ";
                        }
                    }
                }
                return orderby;
            }
        }

        /// <summary>
        /// 排序开始模板
        /// </summary>
        [DataMember]
        public virtual string OrderByTemplate
        {
            get
            {
                return "order by ";
            }
        }
        /// <summary>
        /// 分组模板
        /// </summary>
        [DataMember]
        public virtual string GroupByTemplate
        {
            get
            {
                return "group by ";
            }
        }

        /// <summary>
        /// top N模板
        /// </summary>
        [DataMember]
        public virtual string TopTemplate
        {
            get
            {
                return "top({0})";
            }
        }
        /// <summary>
        /// 最大值 模板
        /// </summary>
        [DataMember]
        public virtual string MaxTemplate
        {
            get
            {
                return "select max({0}) from (" + this.SqlFunStr + ") as derivedtable_1";
            }
        }
        /// <summary>
        /// 最小值 模板
        /// </summary>
        [DataMember]
        public virtual string MinTemplate
        {
            get
            {
                return "select min({0}) from (" + this.SqlFunStr + ") as derivedtable_1";
            }
        }

        /// <summary>
        /// 求和模板
        /// </summary>
        [DataMember]
        public virtual string SumTemplate
        {
            get
            {
                return "select sum({0}) from (" + this.SqlFunStr + ") as derivedtable_1";
            }
        }

        /// <summary>
        /// 平均值模板
        /// </summary>
        [DataMember]
        public virtual string AvgTemplate
        {
            get
            {
                return "select Avg({0}) from (" + this.SqlFunStr + ") as derivedtable_1";
            }
        }

        /// <summary>
        /// count(*) 模板
        /// </summary>
        [DataMember]
        public virtual string CountTemplate
        {
            get
            {
                return "select Count(*) from (" + this.SqlFunStr + ") as derivedtable_1";
            }
        }
        /// <summary>
        /// in 模板
        /// </summary>
        [DataMember]
        public virtual string InTemplate
        {
            get
            {
                return "{0} in ({1}) ";
            }
        }

        /// <summary>
        /// 分页 模板 MsSqlServer 2008/2005
        /// </summary>
        [DataMember]
        public virtual string PageTemplate_2008_2005
        {
            get
            {
                var sql = new StringBuilder();
                var _temp = "";

                sql.Append("select");
                sql.Append(" " + "row_number() over(" + this.OrderByStr + ") AS 'RowNumber',");
                sql.Append(" " + this.FieldsStr);
                sql.Append(" " + "from");
                sql.Append(" " + this.TableName);
                sql.Append(" " + this.WhereStr);

                _temp = string.Format("select * from ({0}) as derivedtable_1 where (RowNumber between {{0}} and {{1}}) {1}", sql.ToString(), this.OrderByStr);
                return _temp;
            }
        }

        /// <summary>
        /// 分页 模板
        /// </summary>
        [DataMember]
        public virtual string PageTemplate
        {
            get
            {
                var sql = new StringBuilder(); 
                sql.Append("select");
                sql.Append(" " + this.FieldsStr);
                sql.Append(" " + "from");
                sql.Append(" " + this.TableName);
                sql.Append(" " + this.WhereStr);
                sql.Append(" " + this.OrderByStr);
                sql.Append(" " + "offset {0} row fetch next {1} rows only");
                var _temp = sql.ToString();
                return _temp;
            }
        }
        #endregion

        public QueryEntity(LambdaToSql.EntityModel.DbContext context) : base(context)
        {
            Context = context;
            this.Where = new StringBuilder();

            this.OrderBy = new List<string>();
            this.Fields = new List<string>();
            this.GroupBy = new List<string>();
            InitParameters();
        }

        /// <summary>
        /// 初始化Parameters
        /// </summary>
        public virtual void InitParameters()
        {
            this.Parameters = new List<LambdaToSql.EntityModel.DataParameter>();
        }
    }
}
